import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_stock_router = APIRouter(prefix="/material/tsStock", tags=["仓库管理"], dependencies=[Depends(oauth2_scheme)])


class WarehouseSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fstockname: Optional[str] = Field("", description="仓库名称（支持前缀匹配）")
    fparentNo: Optional[str] = Field("", description="父级仓库编号")
    ftype: Optional[int] = Field(None, description="仓库类型编码")


@data_stock_router.get("/list", summary="仓库查询接口")
def list_stock(search: WarehouseSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.i_ts_stock where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fstockname != "":
        sql = sql + " and fstockname like concat('%', :fstockname ,'%')"
        param["fstockname"] = search.fstockname
    if search.fparentNo != "":
        sql = sql + " and fparent_no = :fparentNo "
        param["fparentNo"] = search.fparentNo
    if search.ftype != "":
        sql = sql + " and ftype = :ftype "
        param["ftype"] = search.ftype
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fstockno,fstockname,fparent_no,fsite_no,fmemo,fvideo_url,fperson,"
    sql = sql + " fpoint_state,fupddate,foperator,ftype"
    sql = sql + " from lims.i_ts_stock where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fstockname != "":
        sql = sql + " and fstockname like concat('%', :fstockname ,'%')"
        param["fstockname"] = search.fstockname
    if search.fparentNo != "":
        sql = sql + " and fparent_no = :fparentNo "
        param["fparentNo"] = search.fparentNo
    if search.ftype != "":
        sql = sql + " and ftype = :ftype "
        param["ftype"] = search.ftype
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fstockno'] = data[key][0]
        d1['fstockname'] = data[key][1]
        d1['fparentNo'] = data[key][2]
        d1['fsiteNo'] = data[key][3]
        d1['fmemo'] = data[key][4]
        d1['fvideourl'] = data[key][5]
        d1['fperson'] = data[key][6]
        d1['fpointState'] = data[key][7]
        d1['fupddate'] = data[key][8]
        d1['foperator'] = data[key][9]
        d1['ftype'] = data[key][10]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class WarehouseCreate(BaseModel):
    """仓库资料新增请求参数"""
    fstockno: str = Field(..., description="仓库编号（唯一标识）")
    fstockname: str = Field(..., description="仓库名称")
    fsiteNo: str = Field(..., description="站点编号")
    fpointState: int = Field(..., description="点位状态编码")
    fperson: str = Field(..., description="负责人")
    ftype: int = Field(..., description="仓库类型编码")
    fparentNo: str = Field(..., description="父级仓库编号")
    fmemo: str = Field("", description="备注信息")
    fvideoUrl: str = Field("", description="视频地址")


@data_stock_router.post("/create", summary="仓库资料新增接口")
def create_stock(create: WarehouseCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select fstockno from lims.i_ts_stock where fstockno= :fstockno "
            data = db.execute(text(sql), {"fstockno": create.fstockno}).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="编号已存在")
            else:
                sql = "insert into lims.i_ts_stock(fstockno,fstockname,fparent_no,fsite_no,fmemo,fvideo_url,fperson,fpoint_state,fupddate,foperator,ftype)"
                sql = sql + " values( :fstockno , :fstockname , :fparentNo , :fsiteNo , :fmemo , :fvideoUrl , :fperson , :fpointState ,now(), :fperson , :ftype );"
                db.execute(text(sql), {
                    "fstockno": create.fstockno,
                    "fstockname": create.fstockname,
                    "fparentNo": create.fparentNo,
                    "fsiteNo": create.fsiteNo,
                    "fmemo": create.fmemo,
                    "fvideoUrl": create.fvideoUrl,
                    "fperson": create.fperson,
                    "fpointState": create.fpointState,
                    "ftype": create.ftype,
                })
                # 返回
                d1 = {}
                d1['fstockno'] = create.fstockno
                d1['fstockname'] = create.fstockname
                d1['fparentNo'] = create.fparentNo
                d1['fsiteNo'] = create.fsiteNo
                d1['fmemo'] = create.fmemo
                d1['fvideourl'] = create.fvideoUrl
                d1['fperson'] = create.fperson
                d1['fpointState'] = create.fpointState
                d1['fupddate'] = None
                d1['foperator'] = create.fperson
                d1['ftype'] = create.ftype
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_stock_router.post("/update", summary="仓库修改接口")
def update_stock(update: WarehouseCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update lims.i_ts_stock"
            sql = sql + " set fstockname= :fstockname ,fparent_no= :fparentNo ,ftype= :ftype "
            sql = sql + ",fmemo= :fmemo ,fvideo_url= :fvideoUrl ,fperson= :fperson "
            sql = sql + ",fpoint_state= :fpointstate ,fupddate=now(),foperator= :userid "
            sql = sql + " where fstockno= :fstockno and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fstockname": update.fstockname,
                "fparentNo": update.fparentNo,
                "ftype": update.ftype,
                "fmemo": update.fmemo,
                "fvideoUrl": update.fvideoUrl,
                "fperson": update.fperson,
                "fpointstate": update.fpointState,
                "userid": user_id,
                "fstockno": update.fstockno,
                "fsiteNo": update.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_stock_router.post("/delete/{fstockno}", summary="仓库删除接口")
def delete_stock(fstockno: str = Path(description="仓库编号"), db: Session = Depends(get_db),
                 token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select sum(f1) "
            sql = sql + " from ( "
            sql = sql + " select count(1) f1 from i_tc_rec_head where fstockno= :fstockno "
            sql = sql + " union"
            sql = sql + " select count(1) f1 from i_tc_good_inv where fstockno= :fstockno ) a"
            sql = sql + " where 1=1"
            data = db.execute(text(sql), {
                "fstockno": fstockno,
            }).fetchall()
            if data[0][0] > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from lims.i_ts_stock where fstockno= :fstockno "
                db.execute(text(sql), {
                    "fstockno": fstockno,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
